package com.yidu.news.dao;


import com.yidu.news.domain.User;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

@Repository
public class UserDao {

    @Resource
    JdbcTemplate jdbcTemplate ;

    /**
     * 增加的方法
     * @param user
     * @return
     */
    public int add(User user){
        String sql = "insert into ws_user(user_id,user_name,user_account,user_password,remark,file)" +
                " values(null,?,?,?,?,?)";
        int rows = jdbcTemplate.update(sql,user.getUserName(),user.getUserAccount(),user.getUserPassword(),user.getRemark(),user.getFile());
        return rows ;
    }

    public int update(User user){
        String sql = "update ws_user set user_name=? ,user_account=?,user_password=?,remark=?,file=? where user_id =?";
        int rows = jdbcTemplate.update(sql,user.getUserName(),user.getUserAccount(),
                user.getUserPassword(),user.getRemark(),user.getFile(),user.getUserId());
        return rows ;
    }

    /**
     * 查询用户名和密码是否正确
     * @param userName
     * @param userPwd
     * @return
     */
    public User queryLogin(String userName,String userPwd){
        /*String sql = "select user_id userId,user_name userName,user_account userAccount ," +
                " user_password userPassword,remark remark,user_status userStatus " +
                "  from ws_user where user_name=? and user_password=?";*/
        String sql = "select *   from ws_user where user_name=? and user_password=?";
        try {
            User user = (User) jdbcTemplate.queryForObject(sql, new Object[]{userName, userPwd}, new BeanPropertyRowMapper(User.class));
            System.out.println("执行:"+sql+"    "+user.getUserName());
            return user ;
        }catch (EmptyResultDataAccessException e){
            e.printStackTrace();
            //如果发生找不到数据的异常,那么就直接返回空
            return null ;
        }catch (IncorrectResultSizeDataAccessException e){
            e.printStackTrace();
            //查询到多条的情况
            return null ;
        }


    }

    /**
     * 根据用户ID查询用户
     * @param userId
     * @return
     */
    public User queryById(Integer userId){

        String sql = "select *   from ws_user where user_id=?";
        try {
            User user = (User) jdbcTemplate.queryForObject(sql, new Object[]{userId}, new BeanPropertyRowMapper(User.class));
            System.out.println("执行:"+sql+"    "+user.getUserName());
            return user ;
        }catch (EmptyResultDataAccessException e){
            e.printStackTrace();
            //如果发生找不到数据的异常,那么就直接返回空
            return null ;
        }catch (IncorrectResultSizeDataAccessException e){
            e.printStackTrace();
            //查询到多条的情况
            return null ;
        }


    }

    /**
     * 查询所有
     * @return
     */
    public List<User> queryAll(User user){
        System.out.println("第几页:"+user.getPage()+"  ");
        String sql = "select * from ws_user where 1=1 ";

        if(user.getUserName()!=null && !"".equals(user.getUserName())){
            sql+=" and user_name like '%"+user.getUserName()+"%'" ;
        }

        sql += "limit ?,?";
        //1,10
        //224236
        List<User> list = jdbcTemplate.query(sql,new BeanPropertyRowMapper<User>(User.class),
                user.getStartRows(),user.getLimit());
        System.out.println(sql);
        return list ;

    }

    /**
     * 查询行数
     * @return
     */
    public int count(User user){
        String sql = "select count(*) from ws_user where 1=1 " ;

        if(user.getUserName()!=null && !"".equals(user.getUserName())){
            sql+=" and user_name like '%"+user.getUserName()+"%'" ;
        }

        Integer count = jdbcTemplate.queryForObject(sql,Integer.class);
        return count ;
    }

    /**
     * 删除用户
     * @param userId
     * @return
     */
    public int deleteById(Integer userId){
        String sql = "delete from ws_user where user_id=?" ;
        return  jdbcTemplate.update(sql,userId);

    }

}
